﻿using MySql.Data.MySqlClient;
using Server.CustomObjects;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Text;
using System.Threading.Tasks;

namespace Server.DataBase
{
    class DBUser : Server.Interfaces.IDataBaseUser
    {
        MySqlConnection mConnection;

        public DBUser(MySqlConnection xConnection)
        {
            this.mConnection = xConnection;
        }
        
        public EndPoint getEndPointForID(Int32 userID)
        {
            MySqlCommand command = null;
            MySqlDataReader Reader = null;

            if (userID < 1) return null;

            try
            {
                command = mConnection.CreateCommand();
                command.CommandText = " SELECT " + Globals.UserFields.IP + "," + Globals.UserFields.PORT + " FROM " + Globals.Tables.USER + " WHERE " + Globals.UserFields.ID + " = " + userID + "";

                Reader = command.ExecuteReader();
                Reader.Read();
                String _ip = Convert.ToString(Reader[Globals.UserFields.IP]);
                Int32 _port = Convert.ToInt32(Reader[Globals.UserFields.PORT]);
                Reader.Close();
                return new IPEndPoint(IPAddress.Parse(_ip), _port);

            }
            catch (Exception e)
            {
                Log.Write(Log.eLogType.DataBase, "--------------SQL ERROR---------------" + e.Message + "\nQuery:\n" + command.CommandText + "------------------------------------");
                return null;
            }
            finally
            {
                if (!Reader.IsClosed) Reader.Close();
            }

        }
        
        public Int32 verifyUserData(string userName, string password, EndPoint pEndpoint)
        {
            MySqlCommand command = null;
            MySqlDataReader Reader = null;
            Int32 _Output = -1;
            IPEndPoint _pEndpoint = (IPEndPoint)pEndpoint;
            if (userName.Length < 1 || password.Length < 1) return -1;
            try
            {
                command = mConnection.CreateCommand();
                command.CommandText = " SELECT " + Globals.UserFields.PASSWORD + "," + Globals.UserFields.ID + " FROM " + Globals.Tables.USER + " WHERE " + Globals.UserFields.NAME + " = '" + userName + "'";

                Reader = command.ExecuteReader();
                Reader.Read();
                String _pw = Convert.ToString(Reader[Globals.UserFields.PASSWORD]);
                Int32 _id = Convert.ToInt32(Reader[Globals.UserFields.ID]);
                Reader.Close();
                if (_pw.Equals(password))
                {

                    command = mConnection.CreateCommand();
                    command.CommandText = "UPDATE " + Globals.Tables.USER +
                                " SET " +
                                Globals.UserFields.LASTLOGIN + " = CURRENT_TIMESTAMP , " +
                                Globals.UserFields.IP + " =  '" + _pEndpoint.Address + "'" +
                                " WHERE " +
                                Globals.UserFields.NAME + " = '" + userName + "'";

                    command.ExecuteNonQuery();

                    //TODO => 1 durch die tatsächliche ID ersetzten
                    _Output = _id;
                }
            }
            catch (Exception e)
            {
                Log.Write(Log.eLogType.DataBase, "--------------SQL ERROR---------------" + e.Message + "\nQuery:\n" + command.CommandText + "------------------------------------");
            }
            finally
            {
                if (Reader != null)
                    if (!Reader.IsClosed)
                        Reader.Close();
                Reader = null;
                command = null;
            }
            return _Output;
        }

        public Boolean updateUser(User user)
        {
            String cmdText = "";
            MySqlCommand cmd = null;
            Boolean _Output = false;
            try
            {
                cmdText = "UPDATE " + Globals.Tables.USER +
                            " SET " +
                            Globals.UserFields.NAME + " =?Name" + "," +
                            Globals.UserFields.X + " =?PosX " + "," +
                            Globals.UserFields.Y + " =?PosY " +
                            " WHERE " +
                            Globals.UserFields.ID + " = " + user.ID;

                cmd = new MySqlCommand(cmdText, mConnection);

                cmd.Parameters.Add("@Name", MySqlDbType.String).Value = user.Name;
                cmd.Parameters.Add("@PosX", MySqlDbType.Decimal).Value = user.X;
                cmd.Parameters.Add("@PosY", MySqlDbType.Decimal).Value = user.Y;

                _Output = Convert.ToBoolean(cmd.ExecuteScalar());
            }
            catch (Exception e)
            {
                _Output = false;
                Log.Write(Log.eLogType.DataBase, "--------------SQL ERROR---------------" + e.Message + "\nQuery:\n" + cmdText + "------------------------------------");
            }
            finally
            {

                cmdText = "";
                cmd = null;
            }
            return _Output;
        }
        public User AddUser(String userName, String Password, String EMail)
        {
            MySqlCommand command = null;
            User _User = null;
            int _id;
            try
            {
                command = mConnection.CreateCommand();
                command.CommandText = "INSERT INTO " + Globals.Tables.USER +
                                    " (" + Globals.UserFields.ID + "," +
                                        Globals.UserFields.NAME + "," +
                                        Globals.UserFields.PASSWORD + "," +
                                        Globals.UserFields.EMAIL +
                                        ") VALUE (NULL,?userName,?Password,?EMail)";
                
                command.Parameters.Add("@userName", MySqlDbType.String).Value = userName;
                command.Parameters.Add("@Password", MySqlDbType.String).Value = Password;
                command.Parameters.Add("@EMail", MySqlDbType.String).Value = EMail;

                command.ExecuteNonQuery();

                _id = Convert.ToInt32(command.LastInsertedId);
                if (_id <= 0) return null;
                _User =this.GetUser(_id);
            }
            catch (Exception e)
            {
                _User = new User(-1);
                Log.Write(Log.eLogType.DataBase, "--------------SQL ERROR---------------\n" + e.Message + "\nQuery:\n" + command.CommandText + "------------------------------------");
            }
            finally
            {
                command = null;
            }
            if (_User == null) _User = new User(-1);
            return _User;
        }

        /// <summary>
        /// Remove's the database userrecord of the given userobject
        /// </summary>
        /// <param name="user"></param>
        /// <returns>true, if the user has been deleted</returns>
        public Boolean RemoveUser(User user)
        {

            if (user == null) return false;

            String cmdText = "";
            MySqlCommand cmd = null;
            Boolean _return;
            try
            {
                cmdText = "DELETE FROM " + Globals.Tables.USER + " WHERE " +
                           Globals.UserFields.ID + " = " + user.ID;

                cmd = new MySqlCommand(cmdText, mConnection);
                _return = Convert.ToBoolean(cmd.ExecuteScalar());
            }
            catch (Exception e)
            {
                _return = false;
                Log.Write(Log.eLogType.DataBase, "--------------SQL ERROR---------------" + e.Message + "\nQuery:\n" + cmdText + "------------------------------------");
            }
            finally
            {
                cmd = null;
                cmdText = "";
            }
            return _return;
        }

        public long Count()
        {
            String cmdText = "SELECT COUNT( " + Globals.UserFields.ID + ") FROM " + Globals.Tables.USER;

            MySqlCommand cmd = new MySqlCommand(cmdText, mConnection);

            return Convert.ToInt64(cmd.ExecuteScalar());
        }
        public Boolean checkExistingUserName(String userName)
        {
            MySqlCommand command = null;
            MySqlDataReader Reader = null;
            Boolean _return;
            try
            {
                command = mConnection.CreateCommand();
                command.CommandText = " SELECT -1 as verify FROM " + Globals.Tables.USER +
                                        " WHERE " + Globals.UserFields.NAME + " = ?userName  LIMIT 1";

                command.Parameters.Add("@userName", MySqlDbType.String).Value = userName;
                
                _return = Convert.ToBoolean(command.ExecuteScalar());
                return _return;
            }
            catch (Exception e)
            {
               
                Log.Write(Log.eLogType.DataBase, "--------------SQL ERROR---------------" + e.Message + "\nQuery:\n" + command.CommandText + "------------------------------------");
                return false;
            }

            finally
            {
                if (Reader != null)
                    if (!Reader.IsClosed)
                        Reader.Close();
                Reader = null;
                command = null;
            }
        }

        public User GetUser(int id)
        {
            User _User;
            MySqlCommand command = mConnection.CreateCommand();
            MySqlDataReader Reader = null;

            try
            {
                command.CommandText = "SELECT " + Globals.UserFields.ID + "," +
                                   Globals.UserFields.NAME + "," +
                                   Globals.UserFields.X + "," +
                                   Globals.UserFields.Y + "," +
                                   Globals.UserFields.IP + "" +
                                   " FROM " + Globals.Tables.USER +
                                   " WHERE " + Globals.UserFields.ID + " = " + id;

                Reader = command.ExecuteReader();
                if (!Reader.HasRows) return null;
                Reader.Read();

                _User = new User(Convert.ToInt32(Reader[Globals.UserFields.ID]));
                _User.Name = Reader[Globals.UserFields.NAME].ToString();
                _User.X = Convert.ToDouble(Reader[Globals.UserFields.X]);
                _User.Y = Convert.ToDouble(Reader[Globals.UserFields.Y]);
                _User.EndPoint = new IPEndPoint(IPAddress.Parse(Convert.ToString(Reader[Globals.UserFields.IP])), 1337);


            }
            catch (Exception e)
            {
                _User = null;
                Log.Write(Log.eLogType.DataBase, "--------------SQL ERROR---------------" + e.Message + "\nQuery:\n" + command.CommandText + "------------------------------------");
            }
            finally
            {
                if (Reader != null)
                    if (!Reader.IsClosed)
                        Reader.Close();
                Reader = null;
                command = null;
            }
            return _User;
        }

        public User GetUser(string name)
        {
            User _User;
            MySqlCommand command = mConnection.CreateCommand();
            MySqlDataReader Reader = null;

            try
            {
                command.CommandText = "SELECT " + Globals.UserFields.ID + "," +
                                   Globals.UserFields.NAME + "," +
                                   Globals.UserFields.X + "," +
                                   Globals.UserFields.Y + "," +
                                   Globals.UserFields.IP + "" +
                                   " FROM " + Globals.Tables.USER +
                                   " WHERE " + Globals.UserFields.NAME + " = '" + name +"'";

                Reader = command.ExecuteReader();
                if (!Reader.HasRows) return null;
                Reader.Read();

                _User = new User(Convert.ToInt32(Reader[Globals.UserFields.ID]));
                _User.Name = Reader[Globals.UserFields.NAME].ToString();
                _User.X = Convert.ToDouble(Reader[Globals.UserFields.X]);
                _User.Y = Convert.ToDouble(Reader[Globals.UserFields.Y]);

            }
            catch (Exception e)
            {
                _User = null;
                Log.Write(Log.eLogType.DataBase, "--------------SQL ERROR---------------" + e.Message + "\nQuery:\n" + command.CommandText + "------------------------------------");
            }
            finally
            {
                if (Reader != null)
                    if (!Reader.IsClosed)
                        Reader.Close();
                Reader = null;
                command = null;
            }
            return _User;
        }



        internal bool UpdateGPS(int UserID, string pLat, string pLng)
        {
            String cmdText = "";
            MySqlCommand cmd = null;
            Boolean _Output = false;
            try
            {
                cmdText = "UPDATE " + Globals.Tables.USER +
                            " SET " +
                            Globals.UserFields.X + " =  " + pLat + " ," +
                            Globals.UserFields.Y + " = " + pLng + " " +
                            " WHERE " +
                            Globals.UserFields.ID + " = " + UserID;

                cmd = new MySqlCommand(cmdText, mConnection);

                //cmd.Parameters.Add("@UserID", MySqlDbType.String).Value = UserID;
                //cmd.Parameters.Add("@PosX", MySqlDbType.String).Value = pLat;
                //cmd.Parameters.Add("@PosY", MySqlDbType.String).Value = pLng;

                if (cmd.ExecuteNonQuery() == 1) _Output = true;

            }
            catch (Exception e)
            {
                _Output = false;
                Log.Write(Log.eLogType.DataBase, "--------------SQL ERROR---------------" + e.Message + "\nQuery:\n" + cmdText + "------------------------------------");
            }
            finally
            {

                cmdText = "";
                cmd = null;
            }
            return _Output;
        }

        internal bool UpdateGPS(int UserID,double pLat, double pLng)
        {
            String cmdText = "";
            MySqlCommand cmd = null;
            Boolean _Output = false;
            try
            {
                cmdText = "UPDATE " + Globals.Tables.USER +
                            " SET " +
                            Globals.UserFields.X + " = ?PosX " + " ," +
                            Globals.UserFields.Y + " = ?PosY " +
                            " WHERE " +
                            Globals.UserFields.ID + " = ?UserID";

                cmd = new MySqlCommand(cmdText, mConnection);

                cmd.Parameters.Add("@UserID", MySqlDbType.String).Value = UserID;
                cmd.Parameters.Add("@PosX", MySqlDbType.Double).Value = pLat;
                cmd.Parameters.Add("@PosY", MySqlDbType.Double).Value = pLng;

                if (cmd.ExecuteNonQuery() == 1) _Output = true;
               
            }
            catch (Exception e)
            {
                _Output = false;
                Log.Write(Log.eLogType.DataBase, "--------------SQL ERROR---------------" + e.Message + "\nQuery:\n" + cmdText + "------------------------------------");
            }
            finally
            {

                cmdText = "";
                cmd = null;
            }
            return _Output;
        }
    }
}
